﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Web.Configuration;

namespace ForestryEnterprise
{
    public partial class AddTreeProducts : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.trees.Items.Add(new ListItem("--->"));
                string statement = "select tree_id, tree_name from Tree";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement, connection);

                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    ListItem newTree = new ListItem(reader["tree_name"].ToString(), reader["tree_id"].ToString());
                    this.trees.Items.Add(newTree);
                }

                reader.Close();
                connection.Close();
            }
        }

        protected void trees_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.trees.SelectedIndex > 0)
            {
                this.updateItem.Visible = false;
                updateDataView();
            }
        }

        protected void addProd_Click(object sender, EventArgs e)
        {
            if (this.trees.SelectedIndex > 0)
            {
                //insert product
                string statement = "insert into Product(prod_name, prod_desc, prod_cost) values(@pName, @pDesc, @pCost)";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement, connection);
                command.Parameters.AddWithValue("@pName", this.prodName.Text);
                command.Parameters.AddWithValue("@pDesc", this.prodDesc.Text);
                command.Parameters.AddWithValue("@pCost", float.Parse(this.prodCost.Text));
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                //find id of the last product inserted 
                statement = "select top 1 prod_id from Product order by prod_id desc";
                command = new OleDbCommand(statement, connection);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                reader.Read();
                int lastProductAdded = int.Parse(reader["prod_id"].ToString());
                reader.Close();
                connection.Close();

                //add connection between the product and the tree in the Used relation
                statement = "insert into Used values(@pID, @tID)";
                command = new OleDbCommand(statement, connection);
                command.Parameters.AddWithValue("@pID", lastProductAdded);
                command.Parameters.AddWithValue("@tID", this.trees.SelectedValue);
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
                updateDataView();
            }
        }

        private void updateDataView()
        {
            string statement = "select Product.prod_id, prod_name, prod_cost, prod_desc from Product, Tree, Used where Tree.tree_id = Used.tree_id and Product.prod_id = Used.prod_id and ";
            statement += "tree.tree_id = " + this.trees.SelectedValue;
            this.productsData.SelectCommand = statement;
            this.productsDataView.DataBind();
        }

        protected void productsDataView_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.updateName.Text = this.productsDataView.SelectedRow.Cells[2].Text;
            this.updateCost.Text = this.productsDataView.SelectedRow.Cells[3].Text;
            this.updateDesc.Text = this.productsDataView.SelectedRow.Cells[4].Text;
            this.updateItem.Visible = true;
        }

        protected void cancel_Click(object sender, EventArgs e)
        {
            this.updateItem.Visible = false;
        }

        protected void update_Click(object sender, EventArgs e)
        {
            //update in base
            string statement = "update Product set prod_name = @prodName, prod_cost = @prodCost, prod_desc = @prodDesc where ";
            statement += "prod_id = " + this.productsDataView.SelectedRow.Cells[1].Text;
            OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
            OleDbCommand command = new OleDbCommand(statement, connection);
            command.Parameters.AddWithValue("@prodName", this.updateName.Text);
            command.Parameters.AddWithValue("@prodCost", float.Parse(this.updateCost.Text));
            command.Parameters.AddWithValue("@prodDesc", this.updateDesc.Text);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            this.updateItem.Visible = false;
            updateDataView();
        }
    }
}